package com.weixin.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.weixin.dao.IGoodsDao;
import com.weixin.entity.Goods;
import com.weixin.util.C3p0Util;

public class GoodsDaoImpl implements IGoodsDao {
	C3p0Util c3p0=new C3p0Util();
	
	@Override
	public List<Goods> selectAll(int pageSize,int pageNum) throws SQLException {
		String sql="select * from goods_tb where is_show=1 limit ?,?";
		return c3p0.getqr().query(sql,new BeanListHandler<Goods>(Goods.class),(pageNum-1)*pageSize,pageSize);
	}

	@Override
	public Goods selectById(int goods_id) throws SQLException {
		String sql="select * from goods_tb where goods_id=?";
		return c3p0.getqr().query(sql,new BeanHandler<Goods>(Goods.class),goods_id);
	}

	@Override
	public boolean insertGoods(Goods goods) throws SQLException{
		String sql="insert into goods_tb(goods_name,goods_classification,price,unit,origin,introduce,remark) values(?,?,?,?,?,?,?)";
		int i=c3p0.getqr().update(sql, goods.getGoods_name(),goods.getGoods_classification(),goods.getPrice(),goods.getUnit(),goods.getOrigin(),goods.getIntroduce(),goods.getRemark());
		return i>0;
	}

	@Override
	public boolean updateGoods(Goods goods) throws SQLException {
		String sql="update goods_tb set goods_name=?,goods_classification=?,price=?,unit=?,origin=?,introduce=?,remark=? where goods_id=?";
		int i=c3p0.getqr().update(sql, goods.getGoods_name(),goods.getGoods_classification(),goods.getPrice(),goods.getUnit(),goods.getOrigin(),goods.getIntroduce(),goods.getRemark(),goods.getGoods_id());
		return i>0;
	}

	@Override
	public boolean deleteById(int goods_id) throws SQLException {
		String sql="update goods_tb set is_show=0 where goods_id=?";
		int i=c3p0.getqr().update(sql, goods_id);
		return i>0;
	}

	@Override
	public List<Goods> selectByName(String goods_name) throws SQLException {
		String sql="SELECT * FROM goods_tb WHERE is_show=1 and goods_name LIKE ?";
		return c3p0.getqr().query(sql,new BeanListHandler<Goods>(Goods.class),"%"+goods_name+"%");
	}

	@Override
	public List<Goods> selectByClass(String goods_classification) throws SQLException {
		String sql="SELECT * FROM goods_tb WHERE goods_classification LIKE ? and is_show=1";
		return c3p0.getqr().query(sql, new BeanListHandler<Goods>(Goods.class),"%"+goods_classification+"%");
	}

	@Override
	public List<Goods> selectByGoodsId(int goods_id) throws SQLException {
		String sql="SELECT * FROM goods_tb WHERE goods_id LIKE ? and is_show=1";
		return c3p0.getqr().query(sql, new BeanListHandler<Goods>(Goods.class),"%"+goods_id+"%");
	}

	@Override
	public List<Goods> getAllForRegain(int pageSize, int pageNum) throws SQLException {
		String sql="select * from goods_tb where is_show=0";
		return c3p0.getqr().query(sql,new BeanListHandler<Goods>(Goods.class));
	}

	@Override
	public boolean regainById(int goods_id) throws SQLException {
		String sql="update goods_tb set is_show=1 where goods_id=?";
		int i=c3p0.getqr().update(sql, goods_id);
		return i>0;
	}

	@Override
	public boolean trueDeleteById(int goods_id) throws SQLException {
		String sql="delete from goods_tb where goods_id=?";
		int i=c3p0.getqr().update(sql, goods_id);
		return i>0;
	}

	@Override
	public int getTotalForRegain() throws SQLException {
		String sql="select count(*) from goods_tb where is_show=0";
		return (int)((long)c3p0.getqr().query(sql, new ScalarHandler()));
	}

	@Override
	public int selectAllTotal() throws SQLException {
		String sql="select count(*) from goods_tb where is_show=1";
		return (int)((long)c3p0.getqr().query(sql, new ScalarHandler()));
	}

	@Override
	public List<Goods> getAll() throws SQLException {
		String sql="select * from goods_tb where is_show=1 ";
		return c3p0.getqr().query(sql,new BeanListHandler<Goods>(Goods.class));
	}

}
